import json
import sqlite3
from .config import Config
import os
from mutagen import File
from mutagen.id3 import ID3, APIC, TIT2, TPE1, TALB
from mutagen.easyid3 import EasyID3
import filetype
import eyed3
class LocalMusic():
    def getLocalArtist(self):
        conn = sqlite3.connect(Config.dbPath)
        c = conn.cursor()

        sql = 'SELECT artist  FROM music_album group by artist ORDER BY artist'

        artistList = c.execute(sql)

        # conn.close()
        return artistList

    def getAlbumsByArtist(self, artist):
        conn = sqlite3.connect(Config.dbPath)
        c = conn.cursor()
        sql = 'SELECT * FROM music_album WHERE artist = "'+ artist +'" ORDER BY date ASC'

        albumList = c.execute(sql)
        return albumList

    def getAlbumInfo(self, albumId):
        conn = sqlite3.connect(Config.dbPath)
        c = conn.cursor()
        sql = 'SELECT * FROM music_album WHERE id = ' + str(albumId) + ' LIMIT 1'

        albumInfo = c.execute(sql)

        return list(albumInfo)[0]

    def getAlbumSongsList(self, album, artist):
        conn = sqlite3.connect(Config.dbPath)
        c = conn.cursor()
        sql = 'SELECT * FROM music_songs WHERE album = "' + album + '" and artist = "'+ artist +'" ORDER BY track ASC'
        print(sql)
        songsList = c.execute(sql)
        return songsList

    def setPlayList(self, songList, playing = 0):

        playlist = {
            'songList': songList,
            'playing' : playing
        }
        with open(Config.cachePath + 'play_list.json', 'w', encoding='utf-8') as f:
            json.dump(playlist, f)

    def getPlayingInfo(self):

        try:
            with open(Config.cachePath + 'play_list.json', encoding='utf-8') as f:
                playlist = json.load(f)
                songList = playlist['songList']
                playing = playlist['playing']

                info = songList[playing]
                return info
        except:
            return False

    def playingPre(self):
        try:
            with open(Config.cachePath + 'play_list.json', encoding='utf-8') as f:
                playlist = json.load(f)
                songList = playlist['songList']
                playing = playlist['playing']

                if playing == 0:
                    playing = len(songList) - 1
                else:
                    playing = playing - 1
                info = songList[playing]
                playlist['playing'] = playing

                with open(Config.cachePath + 'play_list.json', 'w', encoding='utf-8') as f1:
                    json.dump(playlist, f1)
                    return info
        except:
            return False

    def playingNext(self):
        try:
            with open(Config.cachePath + 'play_list.json', encoding='utf-8') as f:
                playlist = json.load(f)
                songList = playlist['songList']
                playing = playlist['playing']

                if playing == len(songList) - 1:
                    playing = 0
                else:
                    playing = playing + 1

                info = songList[playing]
                playlist['playing'] = playing
                with open(Config.cachePath + 'play_list.json', 'w', encoding='utf-8') as f1:
                    json.dump(playlist, f1)
                    return info
        except:
            return False

    def getAllSongsList(self):

        conn = sqlite3.connect(Config.dbPath)
        c = conn.cursor()
        sql = 'SELECT * FROM music_songs ORDER BY artist ASC, album ASC,track ASC'
        print(sql)
        songsList = c.execute(sql)
        return songsList

    def loadLocalMusic(self):
        conn = sqlite3.connect(Config.dbPath)
        c = conn.cursor()
        read_file_dir = "D:/音乐"

        for root, dirs, files in os.walk(read_file_dir):
            # 遍历目录里的所有文件
            for filename in files:
                filePath = os.path.join(root, filename).replace('\\', '/')
                with open(filePath, "rb") as f:
                    kind = filetype.guess(f)
                    # print(filePath)
                    try:
                        if kind.extension == 'mp3':
                            info = EasyID3(filePath)
                            # print(info)
                            if "album" in info:
                                album = info['album'][0]
                            else:
                                album = ''
                                continue

                            if "title" in info:
                                title = info['title'][0]
                            else:
                                title = ''
                                continue

                            if "date" in info:
                                date = info['date'][0]
                            else:
                                date = ''

                            if "albumartist" in info:
                                albumartist = info['albumartist'][0]
                            else:
                                albumartist = ''
                                continue

                            if "artist" in info:
                                artist = ','.join(info['artist'])
                            else:
                                artist = ''

                            if "tracknumber" in info:
                                track = info['tracknumber'][0]
                            else:
                                track = '1'

                            # songSql = "SELECT * FROM music_songs WHERE title = '"+ title +"' and album = '"+ album +"'"
                            songSql = 'SELECT * FROM music_songs WHERE title = "' + title + '" and album = "' + album + '"'
                            # print(songSql)
                            songCursor = c.execute(songSql)
                            if len(list(songCursor)) == 0:
                                mp3Info = eyed3.load(filePath)
                                seconds = int(round(mp3Info.info.time_secs, 0))
                                m, s = divmod(seconds, 60)
                                h, m = divmod(m, 60)

                                timeFormat = ''
                                if h > 0:
                                    timeFormat = timeFormat + str(h) + ':'

                                if m > 0:
                                    timeFormat = timeFormat + str(m) + ':'
                                else:
                                    timeFormat = "0:"

                                if s > 0:
                                    if s < 10:
                                        timeFormat = timeFormat + '0' + str(s)
                                    else:
                                        timeFormat = timeFormat + str(s)
                                else:
                                    timeFormat = timeFormat + '00'

                                c.execute(
                                    'INSERT INTO music_songs (`title`,album,`track`,artist,path,duration) VALUES ("' + title + '","' + album + '",' + track + ',"' + artist + '","' + filePath + '","'+ timeFormat +'")')

                                albumSql = 'SELECT * FROM music_album WHERE album = "' + album + '" and artist = "' + albumartist + '"'
                                albumCursor = c.execute(albumSql)
                                if len(list(albumCursor)) == 0:
                                    c.execute(
                                        'INSERT INTO music_album (`album`,artist,`date`) VALUES ("' + album + '","' + albumartist + '","' + date + '")')
                                    audio = File(filePath)
                                    try:
                                        img_data = audio.tags['APIC:'].data
                                        with open(
                                                'D:/workspace/MyMusicPlayer/cache/cover/' + albumartist + '-' + album + '.jpg',
                                                'wb') as img:
                                            img.write(img_data)
                                    except Exception as e:
                                        print('no_cover')
                                        pass

                                    artistSql = 'SELECT * FROM music_artist WHERE `name` = "' + albumartist + '"'
                                    print(artistSql)
                                    artistCursor = c.execute(artistSql)
                                    if len(list(artistCursor)) == 0:
                                        c.execute('INSERT INTO music_artist (`name`) VALUES ("' + albumartist + '")')


                            else:
                                if os.path.exists(
                                        'D:/workspace/MyMusicPlayer/cache/cover/' + albumartist + '-' + album + '.jpg'):
                                    pass
                                else:
                                    print(album)
                                    audio = File(filePath)
                                    try:
                                        img_data = audio.tags['APIC:'].data
                                        with open(
                                                'D:/workspace/MyMusicPlayer/cache/cover/' + albumartist + '-' + album + '.jpg',
                                                'wb') as img:
                                            img.write(img_data)
                                    except Exception as e:
                                        print('no_cover')
                                        print(e)
                                        pass
                                continue
                    except Exception as e:
                        print(e)
                        pass
        # sql = "SELECT * FROM music_album ORDER BY artist desc "
        # sql = "SELECT artist  FROM music_album group by artist ORDER BY artist"
        # album = c.execute(sql)
        # print(list(album))
        conn.commit()
        conn.close()